var db = require('../conf/db')
const jwtUtil = require('../public/utils/jwt')
let dayjs = require('dayjs')
let jwt = new jwtUtil()
    //添加城市时使用
const setCitys = async(req, res) => {
    for (let i = 0, key = 0; i < citys.length; i++) {
        const element = citys[i];
        for (let j = 0; j < 1; j++, key++) {
            const obj = element.children[j];

            let sql = `INSERT INTO mdm.sys_dict ( dict_type, dict_key, dict_value, remark, iseditor, remark1, remark2) VALUES ( 'citys', "${key}", "${obj.name}", '城市', 0, "${obj.log}", "${obj.lat}" )`

            db.query(sql)
        }

    }


    // db.query(sql, [], function(results, fields) {
    //     // 以json的形式返回
    //     res.send({ code: 20000, message: '成功' })


    // })
}

const staticFacNumber = async(req, res) => {

    let query=req.body
    let sql = `SELECT
	sum( CASE MONTH ( t1.createDate ) WHEN '1' THEN 1 ELSE 0 END ) AS '1',
	sum( CASE MONTH ( t1.createDate ) WHEN '2' THEN 1 ELSE 0 END ) AS '2',
	sum( CASE MONTH ( t1.createDate ) WHEN '3' THEN 1 ELSE 0 END ) AS '3',
	sum( CASE MONTH ( t1.createDate ) WHEN '4' THEN 1 ELSE 0 END ) AS '4',
	sum( CASE MONTH ( t1.createDate ) WHEN '5' THEN 1 ELSE 0 END ) AS '5',
	sum( CASE MONTH ( t1.createDate ) WHEN '6' THEN 1 ELSE 0 END ) AS '6',
	sum( CASE MONTH ( t1.createDate ) WHEN '7' THEN 1 ELSE 0 END ) AS '7',
	sum( CASE MONTH ( t1.createDate ) WHEN '8' THEN 1 ELSE 0 END ) AS '8',
	sum( CASE MONTH ( t1.createDate ) WHEN '9' THEN 1 ELSE 0 END ) AS '9',
	sum( CASE MONTH ( t1.createDate ) WHEN '10' THEN 1 ELSE 0 END ) AS '10',
	sum( CASE MONTH ( t1.createDate ) WHEN '11' THEN 1 ELSE 0 END ) AS '11',
	sum( CASE MONTH ( t1.createDate ) WHEN '12' THEN 1 ELSE 0 END ) AS '12' 
FROM
	repairlog t1
	LEFT JOIN facility t2 ON t1.facCode = t2.facCode 
WHERE
	1 = 1 `
if(query.year){
sql+='and YEAR ( t1.createDate ) = '+query.year
}
if(query.deviceType){
    sql+=' and t2.deviceType = '+query.deviceType
    }
    db.query(sql, [], function(results, fields) {
        // 以json的形式返回
        res.send({ code: 20000, data: results })
    })
}
const getPoint = async(req, res) => {
    let sql = `SELECT
	f.location,
	count( 1 ) 'count',
	s.dict_value,
	s.remark1 'lng',
	s.remark2 'lat'
FROM
	facility f
	LEFT JOIN sys_dict s ON s.dict_key = f.location 
WHERE
	s.dict_type = 'citys' 
GROUP BY
	f.location,
	s.dict_value,
	s.remark1,
	s.remark2`
    db.query(sql, [], function(results, fields) {
        // 以json的形式返回
        res.send({ code: 20000, data: results })
    })
}
const getPointMessage = async(req, res) => {
    let body = req.body
    let city = ''

    let sql1 = `SELECT dict_key FROM sys_dict where remark2 = ${body.lat}`
    db.query(sql1, [], function(results, fields) {
        city = results[0].dict_key
        let sql2 = `SELECT
	( SELECT count( * ) FROM facility  WHERE location = ${city}) 'facsum',
	( SELECT count( * ) FROM facility WHERE status = 1 and location = ${city} ) 'fault',
	( SELECT count( * ) FROM facility WHERE status = 0 and location = ${city}) 'normal',
	( SELECT count( * ) FROM facility WHERE status = 2 and location = ${city}) 'maintain'`
        db.query(sql2, [], function(datas, fields) {
            // 以json的形式返回
            res.send({ code: 20000, data: datas })
        })
    })


}
const getFacCount = async(req, res) => {
    let sql = `SELECT
	( SELECT count( * ) FROM facility ) 'facsum',
	( SELECT count( * ) FROM facility WHERE status = 1 ) 'fault',
	( SELECT count( * ) FROM facility WHERE status = 0 ) 'normal',
	( SELECT count( * ) FROM facility WHERE status = 2 ) 'maintain'`

    db.query(sql, [], function(results, fields) {
        // 以json的形式返回
        res.send({ code: 20000, data: results })


    })
}
const getFacList = async(req, res) => {
    let user = jwt.verifyToken(req.headers['x-token'])
    let sql = 'SELECT * FROM `mdm`.`facility`'
    let loginName=user.data.loginName
    let data = req.body
    // if(data.role=='admin'||data.role=='editor'||data.role=='serviceman'){
    //     loginName=data.loginName?data.loginName:''
    // }
    if (data.facName || data.facCode || data.status||data.loginName||data.role) {
        sql += ' WHERE 1=1 '
        if (data.facName) {
            sql += ' and facName like "%' + data.facName + '%"'
        }
        if (data.status) {
            sql += ' and status = "' + data.status + '"'
        }
        if (data.facCode) {
            sql += ' and facCode like "%' + data.facCode + '%"'
        }
        if (data.loginName) {
            sql += ' and loginName like "%' + data.loginName + '%"'
        }

    }
    if(loginName!='admin'){
        sql += 'and loginName  is null or loginName="' + loginName + '"'
    }
    sql += 'and id >= (select id from facility order by id limit '+ (data.listQuery.page-1)*data.listQuery.limit +', 1) ORDER  BY createDate DESC limit  ' +data.listQuery.limit
    console.log(sql)
    db.query(sql, [], function(results, fields) {
        // db.query('SELECT count(id) count FROM mdm.facility', [], function(result, fields) {
             // 以json的形式返回
        res.send({ code: 20000, data: {items:results,total:results.length} })
        // })

       


    })
}
//绑定-解绑设备
const bindFac = async(req, res) => {
    let user = jwt.verifyToken(req.headers['x-token'])
    let loginName=user.data.loginName
    let data = req.body
    let sql = 'UPDATE `mdm`.`facility` SET `loginName` = "'+loginName+'" WHERE `id` = '+data.id
    console.log(sql)
    db.query(sql, [], function(results, fields) {
        res.send({ code: 20000, data:results})
    })
}
const unBindFac = async(req, res) => {
    let user = jwt.verifyToken(req.headers['x-token'])
    let loginName=user.data.loginName
    let data = req.body
    let sql = 'UPDATE `mdm`.`facility` SET `loginName` = NULL WHERE `id` = '+data.id
    
    db.query(sql, [], function(results, fields) {
        res.send({ code: 20000, data:results})
    })
}
const getFaultList = async(req, res) => {
    let user = jwt.verifyToken(req.headers['x-token'])
    let loginName=user.data.loginName

    let sql = 'SELECT f.*,e.*,l.* FROM `mdm`.`errorlog` l, errorinfo e,facility f where 1=1 and l.errorId=e.errorId and f.facCode = l.MachineNo '
    let data = req.body
        if (data.grade) {
            sql += ' and e.grade = "' + data.grade + '"'
        }
        if (data.facCode) {
            sql += ' and f.facCode like "%' + data.facCode + '%"'
        }
        if (loginName!='admin') {
            sql += ' and f.loginName = "' + loginName + '"'
        }
        sql+='ORDER BY l.ErrorTime DESC'
    console.log(sql)
    db.query(sql, [], function(results, fields) {
        // 以json的形式返回
        let arr=[]
        for (let i = 0; i < results.length; i++) {
            let flag=true;
            let count=1;
            results[i].count=count
            results[i].ErrorTime = dayjs(results[i].ErrorTime).format('YYYY-MM-DD HH:mm:ss')
            if(!arr.length) {
                arr.push(results[i])
                continue;
            }
            for (let j = 0; j < arr.length; j++) {
                const obj = arr[j];
                if(results[i].MachineNo==obj.MachineNo&&results[i].ErrorID==obj.ErrorID){
                    obj.count+=1;
                    flag=false;
                    break;
                }
            }
            if(flag) 
            arr.push(results[i])
        }
        res.send({ code: 20000, data: {total:arr.length,arr:arr.splice((data.listQuery.page-1) * data.listQuery.limit,data.listQuery.limit)} })
    }) 
}
const getRepairList = async(req, res) => {
    let sql = 'select t2.id,t2.repairDate,t2.remark,t2.img,t2.operator,t1.deviceType,t2.facCode,t1.facName,t2.reStatus,t1.company,t2.createid,t2.createDate  FROM facility t1 LEFT JOIN  repairlog t2 ON t2.facCode=t1.facCode WHERE 1=1'
    let data = req.body
    if (data.deviceType || data.facCode || data.reStatus) {

        if (data.deviceType) {
            sql += ' and t1.deviceType = "' + data.deviceType + '"'
        }
        if (data.reStatus) {
            sql += ' and t2.reStatus = "' + data.reStatus + '"'
        }
        if (data.facCode) {
            sql += ' and t1.facCode like "%' + data.facCode + '%"'
        }
    }data.listQuery.limit
    sql += ' and T2.id >= (select id from repairlog order by id limit '+ (data.listQuery.page-1)*data.listQuery.limit +', 1) ORDER  BY t2.createDate DESC limit  ' +data.listQuery.limit

    db.query(sql, [], function(results, fields) {
        // 以json的形式返回
        res.send({ code: 20000, data: {items:results,total:results.length} })


    })
}
const getMaintainList = async(req, res) => {
    let sql = 'select t2.id,t2.maintainBill,t2.remark,t2.status,t2.maintainDate,t1.deviceType,t2.createid,t1.facName,t2.operator,t1.company,t2.img,t2.money  FROM facility t1 JOIN  maintainlog t2 WHERE t2.facCode=t1.facCode'
    let data = req.body
    if (data.deviceType || data.facCode || data.status) {

        if (data.deviceType) {
            sql += ' and t1.deviceType = "' + data.deviceType + '"'
        }
        if (data.status) {
            sql += ' and t2.status = "' + data.status + '"'
        }
        if (data.facCode) {
            sql += ' and t1.facCode like "%' + data.facCode + '%"'
        }
    }
    sql += ' and t2.id >= (select id from maintainlog order by id limit '+ (data.listQuery.page-1)*data.listQuery.limit +', 1) ORDER  BY t2.createDate DESC limit  ' +data.listQuery.limit

    // sql += ' ORDER  BY t2.createDate DESC'
    db.query(sql, [], function(results, fields) {
        // 以json的形式返回
        for (let i = 0; i < results.length; i++) {
            results[i].createDate = dayjs(results[i].createDate).format('YYYY-MM-DD HH:mm:ss')
            results[i].maintainDate = dayjs(results[i].createDate).format('YYYY-MM-DD HH:mm:ss')

        }
        res.send({ code: 20000, data: {items:results,total:results.length} })


    })
}
const delFac = async(req, res) => {
    let data = req.body
    let sql = 'DELETE FROM `mdm`.`facility` WHERE `id` = "' + data.id + '"'
    db.query(sql, [], function(results, fields) {
        // 以json的形式返回
        res.send({ code: 20000, data: results })
    })
}
const delFault = async(req, res) => {
    let data = req.body
    let sql = 'DELETE FROM `mdm`.`errorinfo` WHERE `errorid` = "' + data.ErrorID + '"'
    db.query(sql, [], function(results, fields) {
        // 以json的形式返回
        res.send({ code: 20000, data: results })
    })
}
const delRepair = async(req, res) => {
    let data = req.body
    let sql = 'DELETE FROM `mdm`.`repairlog` WHERE `id` = "' + data.id + '"'
    db.query(sql, [], function(results, fields) {
        // 以json的形式返回
        res.send({ code: 20000, data: results })
    })
}
const delMaintain = async(req, res) => {
    let data = req.body
    let sql = 'DELETE FROM `mdm`.`maintainlog` WHERE `id` = "' + data.id + '"'
    db.query(sql, [], function(results, fields) {
        // 以json的形式返回
        res.send({ code: 20000, data: results })
    })
}
const insertFac = async(req, res) => {
    let data = req.body
    let user = jwt.verifyToken(req.headers['x-token'])
    let loginName=user.data.loginName
    if(data.role=='admin'||data.role=='editor'||data.role=='serviceman'){
        loginName=data.loginName?data.loginName:''
    }
    let sql = 'INSERT INTO `mdm`.`facility`( `loginName`,`facName`, `facCode`, `status`, `company`, `deviceType`,`location`) VALUES ( "'+loginName+'","' + (data.facName ? data.facName : '') + '", "' + (data.facCode ? data.facCode : '') + '", "' + (data.status ? data.status : '') + '", "' + (data.company ? data.company : '') + '", "' + (data.deviceType ? data.deviceType : '') + '","'+(data.location?data.location:'')+'")'

    db.query(sql, [], function(results, fields) {
        // 以json的形式返回
        res.send({ code: 20000, data: results })


    })
}
const insertRepair = async(req, res) => {
    let data = req.body
    let fileList=data.fileList
    let user = jwt.verifyToken(req.headers['x-token'])
    let imgList=''
    if(fileList.length){
        for (let i = 0; i < fileList.length; i++) {
            imgList+=fileList[i].response.data+';'
             
         }
    }
    let sql = 'INSERT INTO `mdm`.`repairlog`( `repairDate`, `createid`, `operator`, `company`, `facCode`, `img`, `money`,`createDate`,`remark`,`reStatus`) VALUES (   "' + (data.repairDate ? data.repairDate : '') + '", "' + (user.data.loginName ? user.data.loginName : '') + '", "' + (data.operator ? data.operator : '') + '",  "' + (data.company ? data.company : '') + '",  "' + (data.facCode ? data.facCode : '') + '", "' + (data.fileList.length ? imgList : '') + '",  "' + (data.money ? data.money : '') + '",now(),"' + (data.remark ? data.remark : '') + '",0)'

    db.query(sql, [], function(results, fields) {
        // 以json的形式返回
        res.send({ code: 20000, data: results })


    })
}
const editRepair = async(req, res) => {
    let data = req.body
    let sql = 'UPDATE `mdm`.`repairlog` SET '
    for (const key in data) {
        if (data[key]) {
            if (key == 'id' || key == 'createDate' || key == 'deviceType' || key == 'facName' || key == 'edit'|| key == 'fileList') {
                continue
            }
            sql += key + '="' + data[key] + '",'
        }
    }
    sql = sql.substring(0, sql.length - 1)
    sql += ' WHERE `id` = "' + data.id + '"'
    db.query(sql, [], function(results, fields) {
        // 以json的形式返回
        res.send({ code: 20000, data: results })


    })
}
const editFac = async(req, res) => {
    let data = req.body
    let sql = 'UPDATE `mdm`.`facility` SET '
    for (const key in data) {
        if (data[key]) {
            if (key == 'id' || key == 'createDate'||key == 'role') {
                continue
            }
            sql += key + '="' + data[key] + '",'
        }
    }
    sql = sql.substring(0, sql.length - 1)
    sql += ' WHERE `id` = "' + data.id + '"'

    db.query(sql, [], function(results, fields) {
        // 以json的形式返回
        res.send({ code: 20000, data: results })


    })
}
module.exports = {
    getFacCount,
    getFacList,
    insertFac,
    delFac,
    editFac,
    insertRepair,
    editRepair,
    getRepairList,
    delRepair,
    getFaultList,
    delFault,
    delMaintain,
    bindFac,
    unBindFac,
    setCitys,
    getPoint,
    getPointMessage,
    getMaintainList,
    staticFacNumber
}